package com.wframe.project.former.dataservices.products.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;

import com.alibaba.fastjson.JSON;
import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DataBeanUtils;
import com.wframe.framework.database.ParmSqlVo;
import com.wframe.framework.util.ConversionUtil;
import com.wframe.project.former.entity.ProductsAttrvalBean;

public class ProductsDao extends JBaseDao {

	public ProductsDao() {
		super("wb_products");
	}

	public PageResult queryProducts(JdataBean parms, int size, int pageno) throws Exception {
		String sql = "select p.id,p.name,p.code,p.status,p.edittime,p.publishtime,p.orderno,sum(r.amount) totalamount,sum(r.salesnum) salesnum ,t.filepath photourl,t.viewpath photoview,(case when p.edittime is null then p.createtime else p.edittime end) ptime"
				+ " from wb_products p left join wb_products_prices r on p.id=r.productid "
				+ " left join app_pictures t on t.id=p.photo  where status<>-1";
		List parmlist = new ArrayList();
		String order = "";
		if (parms != null) {
			if (StringUtils.isNotBlank(parms.getString("name"))) {
				sql += " and p.name like ?";
				parmlist.add("%" + parms.getString("name") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("code"))) {
				sql += " and p.code like ?";
				parmlist.add("%" + parms.getString("code") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("company"))) {
				sql += " and p.attrvals like ?";
				parmlist.add("%attrcode%B_chanjia%val%" + StringUtils.trimToEmpty(parms.getString("company")) + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("classid"))) {
				if("null".equals(parms.getString("classid"))){
					sql+=" and p.id not in (select productid from wb_products_and_class)";
				}else{
					JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
					sql += " and p.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
					parmlist.add(clsbean.getString("code") + ".%");
					parmlist.add(clsbean.getString("code"));
				}
			}
			if (StringUtils.isNotBlank(parms.getString("status"))) {
				sql += " and p.status=?";
				parmlist.add(parms.getString("status"));
			}
			if (StringUtils.isNotBlank(parms.getString("like"))) {
				sql += " and (p.name like ? or p.code like ?)";
				parmlist.add("%" + parms.getString("like") + "%");
				parmlist.add("%" + parms.getString("like") + "%");
			}
		}
		sql += " group by p.id";
		if (StringUtils.isNotBlank(order))
			sql += " order by " + order;
		else
			sql += " order by ptime desc";
		return getSession().query2(sql, parmlist, size, pageno);
	}
	
	public PageResult queryProductPrices(JdataBean parms, int size, int pageno) throws Exception {
		String sql="select t.*,CONCAT(p.id,'_',t.id) idcode,p.unitname,p.code productcode,p.name productname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
				+ " from wb_products_prices t"
				+" inner join wb_products p on p.id=t.productid"
				+" left join app_pictures s on s.id=t.photo"//销售属性主图片
				+" left join app_pictures sm on sm.id=p.photo"//商品主图片
				+" where p.status<>-1";
		List parmlist = new ArrayList();
		String order = "";
		if (parms != null) {
			if (StringUtils.isNotBlank(parms.getString("name"))) {
				sql += " and p.name like ?";
				parmlist.add("%" + parms.getString("name") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("code"))) {
				sql += " and p.code like ?";
				parmlist.add("%" + parms.getString("code") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("classid"))) {
				JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
				sql += " and p.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
				parmlist.add(clsbean.getString("code") + ".%");
				parmlist.add(clsbean.getString("code"));
			}
			if (StringUtils.isNotBlank(parms.getString("classcode"))) {
				sql += " and p.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
				parmlist.add(parms.getString("classcode") + ".%");
				parmlist.add(parms.getString("classcode"));
			}
			if (StringUtils.isNotBlank(parms.getString("status"))) {
				sql += " and p.status=?";
				parmlist.add(parms.getString("status"));
			}
			if (StringUtils.isNotBlank(parms.getString("like"))) {
				sql += " and (p.name like ? or p.code like ?)";
				parmlist.add("%" + parms.getString("like") + "%");
				parmlist.add("%" + parms.getString("like") + "%");
			}
		}
		sql += " group by p.id";
		if (StringUtils.isNotBlank(order))
			sql += " order by " + order;
		else
			sql += " order by p.orderno,p.id desc";
		PageResult pr= getSession().query2(sql, parmlist, size, pageno);
		List<JdataBean> productlist=pr.getList();
		for(JdataBean product:productlist){
			String photo=product.getString("photoview");
			if(StringUtils.isBlank(photo))photo=product.getString("product_photoview");
			product.put("photo", photo);
			product.put("fullname", product.getString("productname")+" "+product.getDefault("name","").toString());
		}
		return pr;
	}

	/**
	 * 
	 * 功能:所得商品模型属性以及属性值<br>
	 * 参数:<br>
	 * 作者:王海锋<br>
	 * 时间:2017-1-14下午11:14:04<br>
	 */
	public List<JdataBean> queryAllModelAttrAndVal(String classcode, String productid, Integer tag) throws Exception {
		String[] code = classcode.split("\\.");
		int len = code.length;
		String idstr = "";
		for (int i = 1; i <= len; i++) {
			if (!"".equals(idstr))
				idstr += ",";
			idstr += "'" + StringUtils.join(code, ".", 0, i) + "'";
		}
		String sql = "select a.* from wb_products_attr a where a.status>=0 and a.classcode in ("
				+ idstr + ")";
		if (tag != null)
			sql += " and tag=" + tag;
		sql += " order by orderno,id";
		List<JdataBean> list=getSession().query(sql,null);
		if(StringUtils.isNotBlank(productid)){
			JdataBean attrval=getSession().getObject("select attrvals from wb_products where id=?", new Object[]{productid});
			if(StringUtils.isNotBlank(attrval.getString("attrvals"))){
				List<ProductsAttrvalBean> jsonarr=JSON.parseArray(attrval.getString("attrvals"),ProductsAttrvalBean.class);
				for(ProductsAttrvalBean attrbean:jsonarr){
					//将商品基本属性取出
					for(JdataBean bean:list){
						if(bean.getString("id").equals(attrbean.getAttrid().toString())){
							bean.put("val", attrbean.getVal());
							break;
						}
					}
				}
			}
		}
		return list;
	}

	public JdataBean getProductClass(String productid) throws Exception {
		JdataBean bean = getSession().getObject("select * from wb_products_and_class where productid=?",
				new Object[] { productid });
		if (bean == null)
			bean = new JdataBean();
		return bean;
	}
	
	public void saveProductsAttrval(Integer productid, List<ProductsAttrvalBean> list) throws Exception {
		getSession().update("delete from wb_products_attrval where productid=?", new Object[] { productid });
		for (ProductsAttrvalBean bean : list) {
			JdataBean databean=DataBeanUtils.beanToMap(bean);
			databean.remove("valtxt");
			databean.remove("attrcode");
			databean.put("productid", productid);
			getSession().save("wb_products_attrval", databean.getDataMap());
		}
	}

	public void saveProductsPrices(Integer productid, List<JdataBean> list) throws Exception {
		String ids="";
		for (JdataBean bean : list) {
			if(StringUtils.isNotBlank(bean.getString("id"))){
				if(!ids.equals(""))ids+=",";
				ids+=bean.getInt("id");
			}
		}
		if(StringUtils.isNotBlank(ids))
			getSession().update("delete from wb_products_prices where productid=? and id not in ("+ids+")", new Object[] { productid });
		else
			getSession().update("delete from wb_products_prices where productid=?", new Object[] { productid });
		for (JdataBean bean : list) {
			bean.put("productid", productid);
			if(StringUtils.isNotBlank(bean.getString("id"))){
				Map key=new HashMap();
				key.put("id", bean.getString("id"));
				getSession().update("wb_products_prices", bean.getDataMap(),key);
			}else{
				getSession().save("wb_products_prices", bean.getDataMap());
			}
		}
	}

	public List<JdataBean> queryProductsPrices(String productid) throws Exception {

		return getSession().query("select * from wb_products_prices where productid=? order by id",
				new Object[] { productid });
	}

	public void saveProductAndClass(String classid, String classcode, String productid) throws Exception {
		if(StringUtils.isNotBlank(classcode)){
		JdataBean bean = new JdataBean();
		bean.put("classid", classid);
		bean.put("classcode", classcode);
		bean.put("productid", productid);
		JdataBean getb = getSession().getObject("select id from wb_products_and_class where productid=?",
				new Object[] { productid });
		if (getb == null) {
			getSession().save("wb_products_and_class", bean.getDataMap());
		} else {
			Map key = new HashMap();
			key.put("id", getb.getString("id"));
			getSession().update("wb_products_and_class", bean.getDataMap(), key);
		}
		}
	}

	public void delProduct(String id) throws Exception {
		getSession().update("update wb_products set status=-1 where id=?", new Object[] { id });
	}

	public void shelves(String[] proids) throws Exception {
		if (proids != null) {
			ParmSqlVo parmvo = ConversionUtil.arrayToInSql(proids);
			getSession().update("update wb_products set status=2 where id in (" + parmvo.getInSql() + ")",
					parmvo.getParms().toArray());
		}
	}

	public void offshelf(String[] proids) throws Exception {
		if (proids != null) {
			ParmSqlVo parmvo = ConversionUtil.arrayToInSql(proids);
			getSession().update("update wb_products set status=1 where id in (" + parmvo.getInSql() + ")",
					parmvo.getParms().toArray());
		}
	}

	public PageResult queryStockList(JdataBean parms, int size, int pageno) throws Exception {
		String sql = "select p.id,p.name,p.code,s.id attrid,s.name attrname,s.amount totalamount,s.djamount,(ifnull(s.amount,0)-ifnull(s.djamount,0)) amount,t.filepath photourl,t.viewpath photoview"
				+ " from wb_products p left join wb_products_prices s on p.id=s.productid"
				+ " left join app_pictures t on t.id=p.photo"
				+ " where p.status<>-1";
		List parmlist = new ArrayList();
		String order = "";
		if (parms != null) {
			if (StringUtils.isNotBlank(parms.getString("name"))) {
				sql += " and p.name like ?";
				parmlist.add("%" + parms.getString("name") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("code"))) {
				sql += " and p.code like ?";
				parmlist.add("%" + parms.getString("code") + "%");
			}
		}
		if (StringUtils.isNotBlank(order))
			sql += " order by " + order;
		else
			sql += " order by p.name";
		return getSession().query2(sql, parmlist, size, pageno);
	}
	
	public JdataBean getProductsPrices(String id) throws Exception{
		return getSession().getObject("select p.id,p.name,s.id attrid,s.name attrname,s.amount totalamount,s.djamount,(ifnull(s.amount,0)-ifnull(s.djamount,0)) amount"
				+ " from wb_products p left join wb_products_prices s on p.id=s.productid where s.id=?", new Object[]{id});
	}
	public void updateProductsPrices(String id,JdataBean bean) throws Exception{
		Map key=new HashMap();
		key.put("id", id);
		getSession().update("wb_products_prices", bean.getDataMap(),key);
	}
	
	public List<JdataBean> queryControlsales(String productid) throws Exception{
		return getSession().query("select * from wb_products_controlsales where type<>4 and productid=? order by id", new Object[]{productid});
	}
	public List<JdataBean> queryControlUsers(String productid) throws Exception{
//		JdataBean bean=getSession().getObject("select * from wb_products_controlsales where type=4 and productid=?", new Object[]{productid});
//		if(bean!=null && StringUtils.isNotBlank(bean.getString("val"))){
//			String sql = "select u.*,c.name comname,c.idnum comidnum,c.type comtype,c.contacts,c.phone comphone"
//					+ " from wbuser u"
//					+ " left join wb_company c on c.userid=u.id"
//					+ " where u.state<>-1 and u.id in ("+bean.getString("val").substring(0,bean.getString("val").length()-1)+") order by id desc";
			String sql="select u.*,c.name comname,c.idnum comidnum,c.type comtype,c.contacts,c.phone comphone"
					+ " from wbuser u"
					+ " left join wb_company c on c.userid=u.id"
					+ " left join wb_products_controlsales s on s.val=u.id"
					+ " where u.state<>-1 and s.type=4 and s.productid=? order by id desc";
			return getSession().query(sql, new Object[]{productid});
	}
	
	public String addControlsales(JdataBean bean) throws Exception{
		return getSession().saveReturnKey("wb_products_controlsales",bean.getDataMap());
	}
	
	public void delControlsalesAll(String productid) throws HibernateException, Exception{
		getSession().delete("delete from wb_products_controlsales where productid=?",new Object[]{productid});
	}
	
	public List<JdataBean> queryPricegrade(String productid) throws Exception{
		return getSession().query("select * from wb_products_pricegrade where  productid=?", new Object[]{productid});
	}
	public void addPricegrade(JdataBean bean) throws HibernateException, Exception{
		getSession().saveReturnKey("wb_products_pricegrade",bean.getDataMap());
	}
	public void delPricegradeAll(String productid) throws HibernateException, Exception{
		getSession().delete("delete from wb_products_pricegrade where productid=?",new Object[]{productid});
	}
	public void updatecontrol(String productid,int iscontrol) throws Exception{
		getSession().update("update wb_products set iscontrol=? where id=?",new Object[]{iscontrol,productid});
	}
	
	public PageResult queryHotProducts(JdataBean parms, int size, int pageno) throws Exception {
		String sql="select c.id,CONCAT(p.id,'_',t.id) idcode,c.productid,c.orderno,c.priceid,c.status,t.name attrname,t.id attrid,t.price,p.code productcode,p.name productname,p.unitname,p.attrvals,s.filepath photourl,s.viewpath photoview,sm.filepath product_photourl,sm.viewpath product_photoview"
				+ " from wb_hot_products c"
				+" inner join wb_products_prices t on t.id=c.priceid"
				+" inner join wb_products p on p.id=c.productid"
				+" left join app_pictures s on s.id=t.photo"//销售属性主图片
				+" left join app_pictures sm on sm.id=p.photo"//商品主图片
				+" where  p.status<>-1";
		List parmlist=new ArrayList();
		if (parms != null) {
			if (StringUtils.isNotBlank(parms.getString("name"))) {
				sql += " and p.name like ?";
				parmlist.add("%" + parms.getString("name") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("code"))) {
				sql += " and p.code like ?";
				parmlist.add("%" + parms.getString("code") + "%");
			}
			if (StringUtils.isNotBlank(parms.getString("status"))) {
				sql += " and c.status = ?";
				parmlist.add(parms.getString("status"));
			}
			if (StringUtils.isNotBlank(parms.getString("classid"))) {
				JdataBean clsbean = new ProductClassDao().getBean(parms.getString("classid"));
				sql += " and p.id in (select productid from wb_products_and_class where classcode like ? or classcode=?)";
				parmlist.add(clsbean.getString("code") + ".%");
				parmlist.add(clsbean.getString("code"));
			}
		}
		sql+=" order by c.orderno desc,c.id";
		return getSession().query2(sql, parmlist,size,pageno);
	}
	
	public void delHotProductAll() throws HibernateException, Exception{
		getSession().delete("delete from wb_hot_products",null);
	}
	public void saveHotProducts(String[] ids) throws Exception{
		if(ids!=null){
			for(int i=0;i<ids.length;i++){
				String[] ss=ids[i].split("_");
				getSession().update("insert into wb_hot_products(productid,priceid,orderno,status) values (?,?,?,2)",new Object[]{ss[0],ss[1],i});
			}
		}
	}
	public void delHotProduct(String[] ids) throws HibernateException, Exception{
		ParmSqlVo sql=ConversionUtil.arrayToInSql(ids);
		if(!sql.getInSql().equals(""))
		getSession().delete("delete from wb_hot_products where id in ("+sql.getInSql()+")",sql.getParms().toArray());
	}
	public void hotProductStatus(String[] ids,int status) throws HibernateException, Exception{
		ParmSqlVo sql=ConversionUtil.arrayToInSql(ids);
		if(!sql.getInSql().equals("")){
			sql.getParms().add(0, status);
			getSession().delete("update wb_hot_products set status=? where id in ("+sql.getInSql()+")",sql.getParms().toArray());
		}
	}
}
